﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using PPAP.Model;
using System.Data.OleDb;
using System.IO;
using PPAP.BO;
using System.Text.RegularExpressions;

namespace PPAP.Web.Master
{
    public partial class ImportUsers : PageBase
    {
        DataTable data = new DataTable();
        LogBO lbo = new LogBO();
        UsersBO ubo = new UsersBO();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            try
            {
                filePath = UploadFile.FileName;
                string name = filePath.Substring(filePath.LastIndexOf("\\") + 1).ToString();
                string exp = name.Substring(name.LastIndexOf(".") + 1);

                if (exp.ToLower() != "xls" && exp.ToLower() != "xlsx" && exp.ToLower() != "csv")
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "key1", "<script>alert('File format not(.xls|.xlsx|.csv)！');</script>");
                }
                else
                {
                    string FileNam_Time = "";
                    string FName = DateTime.Now.ToString("yyyyMMddHHmmss") + name;
                    FileNam_Time = FName;//日期和名称组合
                    string path = HttpContext.Current.Request.MapPath("../FileUpload/ImportExcel/" + base.CurrentUser.Name + "/");
                    string url = "../FileUpload/QYRD/" + CurrentUser.Name + "/new" + FileNam_Time;
                    DirectoryInfo upDir = new DirectoryInfo(path);
                    if (!upDir.Exists)
                    {
                        upDir.Create();
                    }
                    filePath = path + FileNam_Time;
                    UploadFile.SaveAs(filePath);
                    ImportExcel(filePath);

                }
            }
            catch (Exception ex)
            {
                AlertMessage("Err：" + ex.Message.ToString());
                return;
            }
            finally
            {

            }

        }

        private void ImportExcel(string excelFile)
        {
            DataSet ds = new DataSet();
            OleDbConnection myConn = new OleDbConnection();
            try
            {
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                if (rbt2007.Checked)
                    strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + @";Extended Properties=""Excel 12.0;HDR=YES;""";
                myConn = new OleDbConnection(strCon);
                myConn.Open();
                System.Data.DataTable schemaTable = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });     //获取表名

                if (schemaTable.Rows.Count > 0)
                {
                    string tablename = "Sheet1" + "$";//获到表名 
                    string sql = "select * from [" + tablename + "]";  //从工作表查询。
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn);
                    da.Fill(ds);

                    DataTable dt = new DataTable();
                    dt.Columns.Add("rownum");  //rownum
                    dt.Columns.Add("name1");  //Family name
                    dt.Columns.Add("name2");   //First name
                    dt.Columns.Add("name3");   //UserName
                    dt.Columns.Add("name4");  //Email
                    dt.Columns.Add("name5");  //Mobile phone
                    dt.Columns.Add("name6");  //Department
                    dt.Columns.Add("name7");  //Position
                    dt.Columns.Add("name8");  //Superior
                    dt.Columns.Add("name9"); //Country
                    dt.Columns.Add("name10"); //Sort
                    dt.Columns.Add("name11"); //Execute inspection task?
                    dt.Columns.Add("name12"); //Test Department
                    dt.Columns.Add("name13");  //结果
                    ds.Tables[0].Columns.Add("userid");
                    int num = 0;
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        num++;
                        DataRow r = dt.NewRow();
                        r[0] = num;
                        r[1] = dr[0]; //Family name
                        r[2] = dr[1];//First name
                        r[3] = dr[2];//UserName
                        r[4] = dr[3];//Email
                        r[5] = dr[4];//Mobile phone
                        r[6] = dr[5]; //Department
                        r[7] = dr[6]; //Position
                        r[8] = dr[7]; //Superior
                        r[9] = dr[8]; //Country
                        r[10] = dr[9]; //Sort
                        r[11] = dr[10]; //Execute inspection task?
                        r[12] = dr[11]; //Test Department
                        //r[13] = dr[12]; //Test Department


                        string bopp = Checkpp(r[3].ToString(),r[4].ToString());
                        r[13] = bopp; //JIEGUO
                        dr["userid"] = bopp == "<font color='blue'>success</font>" ? "1" : "0";
                        dt.Rows.Add(r);
                    }
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    if (ds.Tables[0].Rows.Count > 0)
                        btnSave.Visible = true;
                    else
                        btnSave.Visible = false;
                }

                Session["dsexcel"] = ds;
                btnSave.Visible = true;
            }
            catch (Exception ex)
            {
                AlertMessage("Err:" + ex.Message.ToString());
                GC.Collect();
            }
            finally
            {
                myConn.Close();
                myConn.Dispose();
            }
        }


        private string Checkpp(string Username,string Email)
        {
            if (!CheckEmail(Email))
            {
                return "<font color='red'>Email format wrong</font>";
            }
            else if (ubo.GetList(" username='" + Username + "'").Tables[0].Rows.Count > 0)
            {
                return "<font color='red'>User name already exists</font>";
            }
            else if (ubo.CheckEamil(Username))
            {
                return "<font color='red'>User name already exists</font>";
            }
            else if (ubo.CheckEamil(Email))
            {
                return "<font color='red'>Email already exists</font>";
            }
            return "<font color='blue'>success</font>" ;

        }

        public bool CheckEmail(string Email)
        {
            if (!getRegexInput(Email, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"))
            {
                return false;
            }
            else
            {
                return true;
            }

        }


        public bool getRegexInput(string str, string regexStr)//str是要被验证的字符串，regexStr为正则表达式
        {
            Regex rStr = new Regex(regexStr);
            bool re = false;
            if (rStr.IsMatch(str))
            {
                re = true;
            }
            return re;
        }

        /// <summary>
        /// 验证PartNo合法性
        /// </summary>
        /// <param name="PartNo"></param>
        /// <returns></returns>
        private bool CheckPartNo(string PartNo)
        {
            //if (PartNo.Length < 11)
            //    return false;
            //if (PartNo.Substring(0, 4).ToUpper() != "TIG-")
            //    return false;
            //else return true;
            return true;
        }

        /// <summary>
        /// 获取部门
        /// </summary>
        /// <param name="department"></param>
        /// <returns></returns>
        private int GetDepartment(string department)
        {
            switch (department)
            {
                case "BDNT-Quality":
                    return 1;
                case "BDNT-logistic":
                    return 2;
                case "BDNT-procurement":
                    return 3;
                case "BDNT-Test":
                    return 6;
                case "BDNT-R&D":
                    return 4;
                case "Supplier":
                    return 5;
                case "BDNT-IT":
                    return 7;
                default:
                    return 1;
            }
        }

        private int GetTestdepartment(string testdepartment)
        {
            switch (testdepartment)
            {
                case "Corrosion":
                    return 12;
                case "Miscellaneous":
                    return 11;
                case "Colour":
                    return 10;
                case "R&D Development":
                    return 6;
                case "R&D Testing":
                    return 5;
                case "Quality Management":
                    return 4;
                case "Process":
                    return 2;
                default:
                    return 1;
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                if (Session["dsexcel"] != null)
                {

                    int num = 0;
                    DataSet ds = Session["dsexcel"] as DataSet;
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        if (dr["userid"].ToString() != "0")
                        {
                            Model.Users d = new Model.Users();
                            d.DEPARTMENTID = GetDepartment(dr[5].ToString()); ;
                            d.EMAIL = dr[3].ToString() ;
                            d.FAMILYNAME = dr[0].ToString();
                            d.FIRSTNAME = dr[1].ToString();
                            d.ISEXECUTEIONSPECTIONTASK = dr[10].ToString()=="Yes" ? 1 : 0;
                            d.MOBILEPHONE = dr[4].ToString();
                            d.POSITION = dr[6].ToString();
                            d.USERNAME = dr[2].ToString();
                            d.TESTDEPARTMENT = GetTestdepartment(dr[11].ToString());
                            d.Superior = dr[7].ToString();
                            d.Country = dr[8].ToString();
                            d.Sort = dr[9].ToString();
                            d.PASSWORD = "123456";
                            int a= ubo.Add(d);
                            if (a > 0)
                                num++;
                        }
                    }
                    if (num > 0)
                    {
                        lbo.WriteLog(CurrentUser.Id, "ImportPart:" + num);
                        AlertMessage("Saved(" + num + ")");
                    }
                    else
                        AlertMessage("Saved(" + 0 + ")");
                    btnSave.Visible = false;
                }
            }
            catch (Exception ex)
            {
                AlertMessage(ex.Message.ToString());
            }
            finally
            {
                Session["dsexcel"] = null;
            }
        }

    }
}